package cn.server.util;

import java.sql.Date;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;

import cn.server.conf.SQLParameter;

@SuppressWarnings("all")
public class SQLUtil {
	/**
	 * Logger for this class
	 */
	private static final Log logger = LogFactory.getLog(SQLUtil.class);

	public static boolean saveOrUpdate(Object data) {
		Session session = null;
		try {
			session = HibernateSessionFactory.getSession();
			session.beginTransaction();
			if (data instanceof List) {
				// 批量处理
				List<Object> dataList = (List<Object>) data;
				for (int i = 0; i < dataList.size(); i++) {
					session.saveOrUpdate(dataList.get(i));
				}
			} else {// 单个处理
				session.saveOrUpdate(data);
			}
			HibernateSessionFactory.commitSession(session);
		} catch (Throwable e) {
			logger.error("save error!", e);
			return false;
		} finally {
			HibernateSessionFactory.closeSession();
		}
		return true;
	}

	public static void executeSql(String sql, Object... objects) {
		Transaction t = null;
		try {
			Session session = HibernateSessionFactory.getSession();
			t = session.beginTransaction();
			t.begin();
			executeSqlTranc(sql, session, objects);
			t.commit();
		} catch (HibernateException e) {
			if (t != null)
				t.rollback();
			throw new RuntimeException("系统繁忙,请稍后再试...", e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	public static void executeSqlTranc(String sql, Session session, Object... objects) {
		SQLQuery sqlQuery = session.createSQLQuery(sql);
		if (objects != null && objects.length > 0) {
			for (int i = 0; i < objects.length; i++) {
				sqlQuery.setParameter(i, objects[i]);
			}
		}
		sqlQuery.executeUpdate();
	}

	public static void insertByMap(String sql, Map<String, Object> param) {
		if (param == null || param.isEmpty())
			return;
		Transaction t = null;
		try {
			Session session = HibernateSessionFactory.getSession();
			t = session.beginTransaction();
			t.begin();
			insertByMapTranc(sql, param, session);
			t.commit();
		} catch (HibernateException e) {
			if (t != null)
				t.rollback();
			throw new RuntimeException("系统繁忙,请稍后再试...", e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	public static void insertByMapTranc(String sql, Map<String, Object> param, Session session) {
		StringBuilder colBuilder = new StringBuilder();
		StringBuilder valueBuilder = new StringBuilder();
		for (String name : param.keySet()) {
			colBuilder.append(",").append(name);
			Object value = param.get(name);
			valueBuilder.append(",").append(value instanceof Date ? "sysdate" : "?");
		}
		SQLQuery sqlQuery = session.createSQLQuery(sql.replace("{condition}",
				String.format(" (%s) values (%s)", colBuilder.substring(1), valueBuilder.substring(1))));
		int i = 0;
		for (String name : param.keySet()) {
			Object value = param.get(name);
			if (value instanceof Date)
				continue;
			sqlQuery.setParameter(i, value);
			i++;
		}
		sqlQuery.executeUpdate();
	}

	public static void updateByMap(String sql, Map<String, Object> param, Object... condObjs) {
		Transaction t = null;
		try {
			Session session = HibernateSessionFactory.getSession();
			t = session.beginTransaction();
			t.begin();
			StringBuilder builder = new StringBuilder();
			if (param != null) {
				for (String name : param.keySet()) {
					Object object = param.get(name);
					builder.append(",").append(name).append("=").append((object instanceof Date) ? "sysdate" : "?");
				}
			}
			SQLQuery sqlQuery = session.createSQLQuery(sql.replace("{condition}",
					builder.length() == 0 ? "" : builder.substring(1)));
			int i = 0;
			if (param != null) {
				for (String name : param.keySet()) {
					Object object = param.get(name);
					if (object instanceof Date)
						continue;
					sqlQuery.setParameter(i, param.get(name));
					i++;
				}
			}
			if (condObjs != null && condObjs.length > 0) {
				for (int j = 0; j < condObjs.length; j++) {
					sqlQuery.setParameter(i, condObjs[j]);
					i++;
				}
			}
			sqlQuery.executeUpdate();
			t.commit();
		} catch (HibernateException e) {
			if (t != null)
				t.rollback();
			throw new RuntimeException("系统繁忙,请稍后再试...", e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	public static List<Map<String, Object>> listQuery(List<SQLParameter> paramList, String sql, int start, int limit) {
		try {
			Session session = HibernateSessionFactory.getSession();
			int times = (sql.length() - sql.replace("{condition}", "").length()) / 11;
			sql = sql.replace("{condition}", genConditionSQLByParamList(paramList));
			List<String> colList = createColumnList(sql);
			SQLQuery sqlQuery = session.createSQLQuery(sql);
			putToQuery(sqlQuery, paramList, 0);
			for (int i = 0; i < times - 1; i++) {
				putToQuery(sqlQuery, paramList, paramList.size() * (i + 1));
			}
			if (start != -1) {
				sqlQuery.setFirstResult(start);
			}
			if (limit != -1) {
				sqlQuery.setMaxResults(limit);
			}
			List<Object> list = sqlQuery.list();
			List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
			if (list == null || list.size() == 0) {
				return resultList;
			}
			for (int i = 0; i < list.size(); i++) {
				resultList.add(createColMap(list.get(i), colList));
			}
			return resultList;
		} catch (HibernateException e) {
			throw new RuntimeException("系统繁忙,请稍后再试...", e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	public static List<Map<String, Object>> listQuery(String sql, int start, int limit, Object... objs) {
		try {
			Session session = HibernateSessionFactory.getSession();
			List<String> colList = createColumnList(sql);
			SQLQuery sqlQuery = session.createSQLQuery(sql);
			if (objs != null && objs.length > 0) {
				for (int i = 0; i < objs.length; i++) {
					sqlQuery.setParameter(i, objs[i]);
				}
			}
			if (start != -1) {
				sqlQuery.setFirstResult(start);
			}
			if (limit != -1) {
				sqlQuery.setMaxResults(limit);
			}
			List<Object> list = sqlQuery.list();
			List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
			if (list == null || list.size() == 0) {
				return resultList;
			}
			for (int i = 0; i < list.size(); i++) {
				resultList.add(createColMap(list.get(i), colList));
			}
			return resultList;
		} catch (HibernateException e) {
			throw new RuntimeException("系统繁忙,请稍后再试...", e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	public static Map<String, Object> uniqueQueryMap(Map<String, Object> map, String sql) {
		Object obj = uniqueQuery(map, sql);
		if (obj == null)
			return null;
		List<String> colList = createColumnList(sql);
		return createColMap(obj, colList);
	}

	public static Object uniqueQuery(Map<String, Object> map, String sql) {
		try {
			Session session = HibernateSessionFactory.getSession();
			String condStr = createEqualSQLCondition(map).replaceFirst("and", "where");
			sql = sql.replace("{condition}", condStr);
			SQLQuery sqlQuery = session.createSQLQuery(sql);
			putToQuery(sqlQuery, map);
			return sqlQuery.uniqueResult();
		} catch (HibernateException e) {
			throw new RuntimeException("系统繁忙,请稍后再试...", e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	public static Object uniqueQuery(String sql, Object... objs) {
		try {
			Session session = HibernateSessionFactory.getSession();
			SQLQuery sqlQuery = session.createSQLQuery(sql);
			if (objs != null && objs.length > 0) {
				for (int i = 0; i < objs.length; i++) {
					sqlQuery.setParameter(i, objs[i]);
				}
			}
			return sqlQuery.uniqueResult();
		} catch (HibernateException e) {
			throw new RuntimeException("系统繁忙,请稍后再试...", e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	public static List<Object> query(String sql, Object... objs) {
		try {
			Session session = HibernateSessionFactory.getSession();
			SQLQuery sqlQuery = session.createSQLQuery(sql);
			if (objs != null && objs.length > 0) {
				for (int i = 0; i < objs.length; i++) {
					sqlQuery.setParameter(i, objs[i]);
				}
			}
			return sqlQuery.list();
		} catch (HibernateException e) {
			throw new RuntimeException("系统繁忙,请稍后再试...", e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	private static String createEqualSQLCondition(Map<String, Object> param) {
		if (param == null)
			return "";
		StringBuilder sb = new StringBuilder();
		for (String key : param.keySet()) {
			sb.append(" and ").append(key).append(" = ").append("?");
		}
		return sb.toString();
	}

	private static void putToQuery(SQLQuery sq, Map<String, Object> query) {
		if (query == null)
			return;
		Iterator<String> it = query.keySet().iterator();
		for (int i = 0; it.hasNext(); i++) {
			sq.setParameter(i, query.get(it.next()));
		}
	}

	private static void putToQuery(SQLQuery sq, List<SQLParameter> paramList, int beginIndex) {
		if (paramList == null || paramList.size() == 0)
			return;
		for (int i = 0; i < paramList.size(); i++) {
			SQLParameter parameter = paramList.get(i);
			switch (parameter.getType()) {
			case LIKE:
				if (parameter.getValue() != null && !"".equals(parameter.getValue())) {
					sq.setParameter(beginIndex + i, String.format("%%%s%%", parameter.getValue()));
				} else {
					sq.setParameter(beginIndex + i, "%%");
				}
				break;
			default:
				sq.setParameter(beginIndex + i, parameter.getValue());
				break;
			}
		}
	}

	private static List<String> createColumnList(String sql) {
		sql = sql.replaceFirst("select", "").substring(0, sql.indexOf(" from")).trim().replaceFirst(" from", "");
		String[] sqls = sql.replaceFirst(" distinct ", "").split(",");
		List<String> ls = new ArrayList<String>();
		for (int i = 0; i < sqls.length; i++) {
			String col = sqls[i].toLowerCase();
			if (col.contains(" as ")) {
				col = col.split(" as ")[1];
			}
			ls.add(col.trim());
		}
		return ls;
	}

	private static String genConditionSQLByParamList(List<SQLParameter> paramList) {
		if (paramList == null || paramList.size() == 0)
			return "";
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < paramList.size(); i++) {
			SQLParameter parameter = paramList.get(i);
			sb.append(" and ").append(parameter.getName());
			switch (parameter.getType()) {
			case EQUAL:
				sb.append("=");
				break;
			case LIKE:
				sb.append(" like ");
				break;
			case GT:
				sb.append(">");
				break;
			case LT:
				sb.append("<");
				break;
			}
			switch (parameter.getDataType()) {
			case DATE:
				sb.append("to_date(?,'yyyymmddhh24miss')");
				break;
			default:
				sb.append("?");
				break;
			}
		}
		return sb.toString().replaceFirst("and", "where");
	}

	private static Map<String, Object> createColMap(Object resultObj, List<String> colList) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		if (colList.size() == 1) {
			resultMap.put(colList.get(0).toLowerCase(), resultObj);
			return resultMap;
		}
		Object[] objs = (Object[]) resultObj;
		for (int i = 0; i < colList.size(); i++) {
			Object value = objs[i];
			// add by wq for 处理时间
			if (value instanceof Timestamp) {
				value = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(value);
			}
			// end add
			resultMap.put(colList.get(i).toLowerCase(), value);
		}
		return resultMap;
	}
}
